﻿Public Class Confirms
    Public Shared Sub UpdateWithAccountID(ByVal confirmID As Integer, ByVal accountID As String, ByVal updateOtherConfirms As Boolean)
        Dim sqlCommand = "UPDATE Confirms SET AccountID = '{0}' WHERE ID = {1}"
        If updateOtherConfirms Then
            sqlCommand += " OR AgtAccountNum = (SELECT AgtAccountNum FROM Confirms WHERE ID = {1})"
        End If
        sqlCommand = String.Format(sqlCommand, accountID, confirmID)
        DataScript.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Sub UpdateWithCustodianID(ByVal confirmID As Integer, ByVal custodianID As String, ByVal updateOtherConfirms As Boolean)
        Dim sqlCommand = "UPDATE Confirms SET AgtAccountNum = '{0}' WHERE ID = {1}"
        If updateOtherConfirms Then
            sqlCommand += " OR AccountID = (SELECT AccountID FROM Confirms WHERE ID = {1})"
        End If
        sqlCommand = String.Format(sqlCommand, custodianID, confirmID)
        DataScript.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Sub UpdateWithTicker(ByVal confirmID As Integer, ByVal ticker As String, ByVal updateOtherConfirms As Boolean)
        Dim sqlCommand = "UPDATE Confirms SET TickerSymbol = '{0}' WHERE ID = {1}"
        If updateOtherConfirms Then
            sqlCommand += " OR SecurityID = (SELECT SecurityID FROM Confirms WHERE ID = {1})"
        End If
        sqlCommand = String.Format(sqlCommand, ticker, confirmID)
        DataScript.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Sub UpdateWithCusip(ByVal confirmID As Integer, ByVal cusip As String, ByVal updateOtherConfirms As Boolean)
        Dim sqlCommand = "UPDATE Confirms SET SecurityID = '{0}' WHERE ID = {1}"
        If updateOtherConfirms Then
            sqlCommand += " OR TickerSymbol = (SELECT TickerSymbol FROM Confirms WHERE ID = {1})"
        End If
        sqlCommand = String.Format(sqlCommand, cusip, confirmID)
        DataScript.ExecuteCommand(sqlCommand)
    End Sub

    Public Shared Function GetCountWithAccountID(ByVal confirmID As Integer, ByVal accountID As String) As Integer
        Dim db As ArchiveDataContext = DataScript.GetArchiveDataContext()
        Dim items = From p In db.Confirms Where p.AccountID <> "" And p.AccountID = accountID And p.AgtAccountNum = "" And p.ID <> confirmID
        Return items.Count
    End Function

    Public Shared Function GetCountWithCustodianID(ByVal confirmID As Integer, ByVal custodianID As String) As Integer
        Dim db As ArchiveDataContext = DataScript.GetArchiveDataContext()
        Dim items = From p In db.Confirms Where p.AgtAccountNum <> "" And p.AgtAccountNum = custodianID And p.AccountID = "" And p.ID <> confirmID
        Return items.Count
    End Function

    Public Shared Function GetCountWithTicker(ByVal confirmID As Integer, ByVal ticker As String) As Integer
        Dim db As ArchiveDataContext = DataScript.GetArchiveDataContext()
        Dim items = From p In db.Confirms Where p.TickerSymbol <> "" And p.TickerSymbol = ticker And p.SecurityID = "" And p.ID <> confirmID
        Return items.Count
    End Function

    Public Shared Function GetCountWithCusip(ByVal confirmID As Integer, ByVal cusip As String) As Integer
        Dim db As ArchiveDataContext = DataScript.GetArchiveDataContext()
        Dim items = From p In db.Confirms Where p.SecurityID <> "" And p.SecurityID = cusip And p.TickerSymbol = "" And p.ID <> confirmID
        Return items.Count
    End Function

End Class
